package com.company.bookstore.mapper.dataMapper;

import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface DataMapper {

    String startEnd = "<if test='start != null' > " +
            "    where create_time &gt; #{ start }\n" +
            "    and create_time &lt; #{ end }\n" +
            "</if> ";
    String andStartEnd = "<if test='start != null' > " +
            "    and create_time &gt; #{ start }\n" +
            "    and create_time &lt; #{ end }\n" +
            "</if> ";

    String monthCase =
            "when 1 then 1\n" +
            "when 2 then 2\n" +
            "when 3 then 3\n" +
            "when 4 then 4\n" +
            "when 5 then 5\n" +
            "when 6 then 6\n" +
            "when 7 then 7\n" +
            "when 8 then 8\n" +
            "when 9 then 9\n" +
            "when 10 then 10\n" +
            "when 11 then 11\n" +
            "when 12 then 12\n";
    /**
     * 获取对应标签图书销售量
     * @param name
     * @return
     */
    @Select("select sum(sales) from t_book b\n" +
            "LEFT JOIN t_book_category  bc\n" +
            "ON b.id = bc.fk_book_id\n" +
            "LEFT JOIN t_category c\n" +
            "ON bc.fk_category_id = c.id\n" +
            "where name = #{name}")
    int getSum(String name);

    @Select("select IFNULL(sum(hot_click),0) from t_book ")
    int getTotalClick();

    @Select("select count(*) from t_user")
    int getTotalUser();

    @Select("<script>" +
            "select count(*) from t_order\n" +
            "where status = 4\n" +
            andStartEnd +
            "</script>")
    int getTotalOrder(String start, String end);

    @Select("<script>" +
            "select IFNULL(sum(total_price),0) from t_order\n" +
            startEnd +
            "</script>")
    int getTotalPay(String start, String end);

    @Select("<script>" +
            "select count(*) from t_order\n" +
            "where grade &gt;= 4\n" +
            andStartEnd +
            "</script>")
    int getTotalPraise(String start, String end);

    @Select("<script>" +
            "select count(*) from t_order\n" +
            "where grade &lt;= 2\n" +
            andStartEnd +
            "</script>")
    int getTotalBad(String start, String end);

    @Select("<script>" +
            "select IFNULL(sum(num),0) from t_order\n" +
            startEnd +
            "</script>")
    int getTotalSales(String start, String end);

    @Select("select IFNULL(sum(repository_num),0) from t_book")
    int getTotalStock();

    @Select("<script>" +
            "select rank() over (\n" +
            "    order by sum(num) desc) number, name, sum(num) as num\n" +
            "from t_order o\n" +
            "    LEFT JOIN t_book tb\n" +
            "        on o.fk_book_id = tb.id\n" +
            "    LEFT JOIN t_book_category tbc\n" +
            "        on tb.id = tbc.fk_book_id\n" +
            "    LEFT JOIN t_category tc\n" +
            "        on tbc.fk_category_id = tc.id\n" +
            "where book_name is not null\n" +
            "and tc.name is not null" +
            andStartEnd +
            "group by name" +
            "</script>")
    List<Map<String, Integer>> getMostPurchaseCategory(String start, String end);

    @Select("<script>" +
            "select rank() over (\n" +
            "    order by count(*) desc) number, book_name, count(*) as praise\n" +
            "from t_order\n" +
            "    LEFT JOIN t_book tb\n" +
            "        on t_order.fk_book_id = tb.id\n" +
            "where grade >= 4 and\n" +
            "      book_name is not null\n" +
            andStartEnd +
            "group by book_name" +
            "</script>")
    List<Map<String, Integer>> getMostPraiseBook(String start, String end);


    @Select("select rank() over (\n" +
            "    order by sum(hot_click) desc) number, book_name, sum(hot_click) as hot_click\n" +
            "from t_book\n" +
            "where hot_click != 0\n" +
            "group by book_name\n")
    List<Map<String, Integer>> getMostClickBook();

    @Select("<script>" +
            "select rank() over (\n" +
            "    order by sum(num * o.price) desc) number, book_name, sum(num * o.price) as sales\n" +
            "from t_order o\n" +
            "    LEFT JOIN t_book tb\n" +
            "        on o.fk_book_id = tb.id\n" +
            "where book_name is not null\n" +
            andStartEnd +
            "group by book_name\n" +
            "</script>")
    List<Map<String, Object>> getMostSaleBook(String start, String end);

    @Select("select  count(deleted=1 or null) as 'unShelveBook',\n" +
            "        count(*) as 'totalBook',\n" +
            "        IFNULL(cast((count(deleted=1 or null)/count(*) ) * 100 as decimal(6,2)),0) as 'rate'\n" +
            "from t_book\n")
    List<Map<String, Object>> getUnShelveRate(String start, String end);

    @Select("<script>" +
            "select  count(grade &gt;=4 or null) as 'praise',\n" +
            "        count(*) as 'totalEvaluate',\n" +
            "        IFNULL(cast((count(grade &gt;=4 or null) /count(*) ) * 100 as decimal(6,2)),0) as 'rate'\n" +
            "from t_order\n" +
            startEnd +
            "</script>")
    List<Map<String, Object>> getPraiseRate(String start, String end);

    @Select("<script>" +
            "select  count(grade &lt;=2 or null) as 'bad',\n" +
            "        count(*) as 'totalEvaluate',\n" +
            "        IFNULL(cast((count(grade &lt;=2 or null) /count(*) ) * 100 as decimal(6,2)),0) as 'rate'\n" +
            "from t_order\n" +
            startEnd +
            "</script>")
    List<Map<String, Object>> getBadRate(String start, String end);

    @Select("<script>" +
            "select  count(evaluate is not null or null) as 'evaluate',\n" +
            "        count(*) as 'totalEvaluate',\n" +
            "        IFNULL(cast((count(evaluate is not null or null) /count(*) ) * 100 as decimal(6,2)),0) as 'rate'\n" +
            "from t_order\n" +
            startEnd +
            "</script>")
    List<Map<String, Object>> getEvaluateRate(String start, String end);

    @Select("<script>" +
            "select name, count(*) as value\n" +
            "from t_logistics tl\n" +
            "left join t_logistics_type tlt on tl.fk_type_id = tlt.id\n" +
            "left join t_order t on tl.fk_order_id = t.id\n" +
            startEnd +
            "group by name\n" +
            "</script>")
    List<Map<String, Object>> getLogiByTime(String start, String end);


    @Select("<script>" +
            "select  IFNULL(sum(hot_click),0) as 'pay',\n" +
            "        count(*) as 'totalOrder',\n" +
            "        IFNULL(cast((sum(hot_click) /count(*) ) * 100 as decimal(6,2)),0) as 'rate'\n" +
            "from t_order\n" +
            "left join t_book tb on t_order.fk_book_id = tb.id\n" +
            startEnd +
            "</script>")
    List<Map<String, Object>> getClickToPayRate(String start, String end);

    @Select("<script>" +
            "select  count(status=1 or null) as 'pay',\n" +
            "        count(*) as 'totalOrder',\n" +
            "        IFNULL(cast((count(status=1 or null) /count(*) ) * 100 as decimal(6,2)),0) as 'rate'\n" +
            "from t_order\n" +
            startEnd +
            "</script>")
    List<Map<String, Object>> getOrderToPayRate(String start, String end);

    @Select("<script>" +
            "select  count(status=3 or null) as 'pay',\n" +
            "        count(*) as 'totalOrder',\n" +
            "        IFNULL(cast((count(status=3 or null) /count(*) ) * 100 as decimal(6,2)),0) as 'rate'\n" +
            "from t_order\n" +
            startEnd +
            "</script>")
    List<Map<String, Object>> getOrderToCancelRate(String start, String end);

    @Select("SELECT m.cnumber as month, IFNULL(x.num,0) as num\n" +
            "FROM census_month m\n" +
            "         LEFT JOIN(\n" +
            "             select\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end as curmonth,\n" +
            "    sum(num) as num\n" +
            "    from t_order\n" +
            "    group by\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end\n" +
            "    order by curmonth\n" +
            ") x\n" +
            "ON x.curmonth = cnumber\n" +
            "order by month\n" +
            "\n")
    List<Map<String, Object>> getLineData();

    @Select("<script>" +
            "SELECT  IFNULL(x.num,0) as num\n" +
            "FROM census_month m\n" +
            "         LEFT JOIN(\n" +
            "             select\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end as curmonth,\n" +
            "    sum(num) as num\n" +
            "    from t_order\n" +
            startEnd +
            "    group by\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end\n" +
            "    order by curmonth\n" +
            ") x\n" +
            "ON x.curmonth = cnumber\n" +
            "order by cnumber" +
            "</script>")
    List<Integer> getLineData2(String start, String end);

    @Select("<script>" +
            "SELECT cast(IFNULL(x.orders,0) as decimal(10,2)) as orders\n" +
            "FROM census_month m\n" +
            "         LEFT JOIN(\n" +
            "             select\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end as curmonth,\n" +
            "    count(num) as orders\n" +
            "    from t_order\n" +
            startEnd +
            "    group by\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end\n" +
            "    order by curmonth\n" +
            ") x\n" +
            "ON x.curmonth = cnumber\n" +
            "order by cnumber" +
            "</script>")
    List<Integer> getOrderPerMonth(String start, String end);

    @Select("<script>" +
            "SELECT cast(IFNULL(x.nums,0) as decimal(10,2)) as nums\n" +
            "FROM census_month m\n" +
            "         LEFT JOIN(\n" +
            "             select\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end as curmonth,\n" +
            "    sum(num) as nums\n" +
            "    from t_order\n" +
            startEnd +
            "    group by\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end\n" +
            "    order by curmonth\n" +
            ") x\n" +
            "ON x.curmonth = cnumber\n" +
            "order by cnumber" +
            "</script>")
    List<Integer> getNumsPerMonth(String start, String end);

    @Select("<script>" +
            "SELECT cast(IFNULL(x.total_price,0) as decimal(10,2)) as total_price\n" +
            "FROM census_month m\n" +
            "         LEFT JOIN(\n" +
            "             select\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end as curmonth,\n" +
            "    sum(total_price) as total_price\n" +
            "    from t_order\n" +
            startEnd +
            "    group by\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end\n" +
            "    order by curmonth\n" +
            ") x\n" +
            "ON x.curmonth = cnumber\n" +
            "order by cnumber" +
            "</script>")
    List<Integer> getTotalPricePerMonth(String start, String end);

    @Select("<script>" +
            "SELECT cast(IFNULL(x.total_price,0) as decimal(10,2)) as total_price\n" +
            "FROM census_month m\n" +
            "         LEFT JOIN(\n" +
            "             select\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end as curmonth,\n" +
            "    sum(total_price) as total_price\n" +
            "    from t_order\n" +
            startEnd +
            "    group by\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end\n" +
            "    order by curmonth\n" +
            ") x\n" +
            "ON x.curmonth = cnumber\n" +
            "order by cnumber\n" +
            "</script>")
    List<Integer> getTotalPricePerMonthByTime(String start, String end);

    @Select("<script>" +
            "SELECT IFNULL(x.orderOver,0) as orderOver\n" +
            "FROM census_month m\n" +
            "         LEFT JOIN(\n" +
            "             select\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end as curmonth,\n" +
            "    count(IF(status=#{ status },true,null)) as orderOver\n" +
            "    from t_order\n" +
            startEnd +
            "    group by\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end\n" +
            "    order by curmonth\n" +
            ") x\n" +
            "ON x.curmonth = cnumber\n" +
            "order by cnumber\n" +
            "</script>")
    List<Integer> getOrderByStatusPerMonth(int status, String start, String end);

    @Select("<script>" +
            "SELECT cast(IFNULL(x.nums,0) as decimal(10,2)) as nums\n" +
            "FROM census_month m\n" +
            "         LEFT JOIN(\n" +
            "             select\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end as curmonth,\n" +
            "    sum(if(status=#{status},num,0)) as nums\n" +
            "    from t_order\n" +
            startEnd +
            "    group by\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end\n" +
            "    order by curmonth\n" +
            ") x\n" +
            "ON x.curmonth = cnumber\n" +
            "order by cnumber\n" +
            "</script>")
    List<Integer> getNumsByStatusPerMonth(int status, String start, String end);

    @Select("<script>" +
            "SELECT cast(IFNULL(x.total_price,0) as decimal(10,2)) as total_price\n" +
            "FROM census_month m\n" +
            "         LEFT JOIN(\n" +
            "             select\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end as curmonth,\n" +
            "    sum(if(status=#{status},total_price,0)) as total_price\n" +
            "    from t_order\n" +
            startEnd +
            "    group by\n" +
            "    case month(create_time)\n" +
            monthCase +
            "    end\n" +
            "    order by curmonth\n" +
            ") x\n" +
            "ON x.curmonth = cnumber\n" +
            "order by cnumber\n" +
            "</script>")
    List<Integer> getPriceByStatusPerMonth(int status, String start, String end);

    @Select("SELECT cnumber as month\n" +
            "from census_month\n")
    List<Integer> getMonth();

    @Select("select count(*) as orderNum, sum(num) as bookNum, sum(total_price) totalPrice, count(if(status=1,true,null)) as paied\n" +
            "from t_order\n" +
            "where create_time >= date(now()) and create_time < DATE_ADD(date(now()),INTERVAL 1 DAY)\n")
    List<Map<String, Integer>> getTodayData();
}
